# -*- coding: utf-8 -*-

"""
Module implementing Dialog.
"""

import sys,csv
import time
import pymssql
import uuid
import _mssql
import decimal
decimal.__version__
uuid.ctypes.__version__
_mssql.__version__
import configparser
from PyQt5.QtCore import pyqtSlot
from PyQt5.QtWidgets import QDialog, QApplication,QMessageBox,QFileDialog
from PyQt5 import QtWidgets

from Ui_CHECKDB import Ui_Dialog


class Dialog(QDialog, Ui_Dialog):
    """
    Class documentation goes here.
    """
    def __init__(self, parent=None):
        """
        Constructor
        
        @param parent reference to the parent widget
        @type QWidget
        """
        super(Dialog, self).__init__(parent)
        self.setupUi(self)
        conf = configparser.ConfigParser()
        conf.read(".\config.ini")
        self.ipAddress = conf.get("net","$ip")
        projects = conf.get("project","$projectname")
        self.testItem = conf.get("keyitem","$testeditems")
        projectList = projects.split(";")
        for project in projectList:
            self.comboBox_ProjectName.addItem(project)



    
    @pyqtSlot()
    def on_pushButton_reset_clicked(self):
        """
        Slot documentation goes here.
        """
        reply = QMessageBox.question(self, '提示：', '清零前请确认库位实物为零，确实要清零吗？',
                                     QMessageBox.Yes | QMessageBox.No, QMessageBox.No)
        if reply == QMessageBox.Yes:
            self.lcdNumber.display(0)
        else:
            pass


    
    @pyqtSlot()
    def on_pushButton_output_clicked(self):
        """
        Slot documentation goes here.
        """
        # TODO: not implemented yet
        fileName = QFileDialog.getSaveFileName(self,"save File","./","csv files(*.csv)")[0]

        startTime = self.dateTimeEdit_Start.text()
        endTime = self.dateTimeEdit_End.text()

        try:
            conn = pymssql.connect(host=self.ipAddress, user="xy", password="program",
                                   login_timeout=5, database="cit", charset="utf8")
            cur = conn.cursor()
        except:
            QMessageBox.question(self, '提示：', '数据库连接失败，请检查！',
                                 QMessageBox.Yes)
        sql = "IF OBJECT_ID('tested_cit', 'U') IS NULL " \
              "CREATE TABLE tested_cit(CTime V" \
              "ARCHAR(40),prdt VARCHAR(45),Imei VARCHAR(40), sn VARCHAR(40),\
              Iccid VARCHAR (40))"
        cur.execute(sql)
        conn.commit()
        prdt1 = self.comboBox_ProjectName.currentText()
        sql = "select * from tested_cit WHERE prdt = '"+prdt1+"'"
        cur.execute(sql)
        Output = cur.fetchall()

        with open(fileName, 'w',newline='') as csvFile:
            writer = csv.writer(csvFile)
            writer.writerow(["Time","prdt","Imei","sn","iccid"])
            for serachResult in Output:
                if startTime <= serachResult[0] <= endTime:
                    writer.writerow([serachResult[0],serachResult[1],serachResult[2],serachResult[3],serachResult[4]])



    @pyqtSlot()
    def on_pushButton_SELECT_clicked(self):
        """
        Slot documentation goes here.
        """
        self.plainTextEdit_result.clear()
        ctime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
        Imei = self.lineEdit_IMEI.text()[0:15]
        prdt = self.comboBox_ProjectName.currentText()

        try:
            conn = pymssql.connect(host=self.ipAddress, user="xy", password="program",
                                   login_timeout=5, database="cit", charset="utf8")
            cur = conn.cursor()
        except:
            QMessageBox.question(self, '提示：', '数据库连接失败，请检查！',
                                 QMessageBox.Yes)
        sql = "IF OBJECT_ID('tested_cit', 'U') IS NULL " \
                "CREATE TABLE tested_cit(CTime VARCHAR(40),prdt VARCHAR(45),Imei VARCHAR(40), sn VARCHAR(40),\
                Iccid VARCHAR (40))"
        cur.execute(sql)
        conn.commit()

        sql = "SELECT COUNT(*) FROM tested_process WHERE (testeditems='"+ self.testItem +"' AND prdt = '"+prdt+"' and imei = '"+Imei+"')"

        cur.execute(sql)
        search_result = int(cur.fetchall()[0][0])
        conn.commit()

        sql = "SELECT COUNT(*) FROM tested_cit WHERE prdt = '"+prdt+"' and imei = '"+Imei+"'"
        cur.execute(sql)
        search_tested = int(cur.fetchall()[0][0])
        conn.commit()

        if search_tested:
            self.plainTextEdit_result.appendPlainText("已经检查过此IMEI!!!")

        elif search_result:
            sql ="SELECT prdt,imei,sn,iccid FROM tested_process WHERE (testeditems = '"+self.testItem+"' AND prdt = '"+prdt+"' and imei = '"+Imei+"')"
            cur.execute(sql)
            result = cur.fetchall()
            sn = result[0][2]
            iccid = result[0][3]

            self.plainTextEdit_result.appendPlainText("此IMEI在数据库中存在!")
            self.plainTextEdit_result.appendPlainText("IMEI: '"+Imei+"'")
            self.plainTextEdit_result.appendPlainText("sn: '"+sn+"'")
            self.plainTextEdit_result.appendPlainText("iccid: '"+iccid+"'")
            conn.commit()

            sql = "INSERT INTO tested_cit(CTime,prdt,Imei,sn,Iccid) VALUES ('"+ctime+"','"+prdt+"','"+Imei+"','"+sn+"','"+iccid+"')"
            cur.execute(sql)
            conn.commit()
            counter = self.lcdNumber.value()
            self.lcdNumber.display(counter+1)

        else:
            self.plainTextEdit_result.appendPlainText("此IMEI在数据库不存在！")
            self.plainTextEdit_result.appendPlainText("请检查是否有漏测试！")


        cur.close()
        conn.close()
        self.lineEdit_IMEI.clear()
        self.lineEdit_IMEI.setFocus()



if __name__ == '__main__':

    app = QtWidgets.QApplication(sys.argv)
    MainWindow = Dialog()
    MainWindow.show()
    sys.exit(app.exec_())
    